package com.fkn.practice.parsecoints;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;


public class DbAdapter {
	
	//country 
	public static final String KEY_ROWID = "_id";	
	public static final String KEY_SER_ID = "id_c";
	public static final String KEY_COUNTRY_NAME = "country";
	public static final String KEY_COUNTRY_ENAME = "ecountry";
	public static final String KEY_CONTINENT_NAME = "continent";
	public static final String KEY_CAPITAL_NAME = "capital";
	public static final String KEY_SQUERE = "squere";
	public static final String KEY_PEOPLE = "people";
	public static final String KEY_URL_FLAGS = "url";
	public static final String KEY_CORRENCY = "corrency";
	public static final String KEY_OFF_LANG = "offlang";
	public static final String KEY_COORD_X = "coordx";
	public static final String KEY_COORD_Y = "coordy";
	
	//paper 
	public static final String KEY_PROWID = "_idp";
	public static final String KEY_PSER_ID = "id_c";
	public static final String KEY_PNOMINAL = "nominalp";
	public static final String KEY_PYEAR = "yearp";
	public static final String KEY_PAVERS = "aversp";
	public static final String KEY_PREVERS = "reversp";
	
	//coins
	public static final String KEY_CROWID = "_idc";
	public static final String KEY_CSER_ID = "id_c";
	public static final String KEY_CYEAR = "yearc";
	public static final String KEY_INFOAVERS = "infoavers";
	public static final String KEY_CAVERS = "urlavers";
	public static final String KEY_INFOREVERS = "inforevers";
	public static final String KEY_CREVERS = "urlrevers";
	
	
	
	private static final String TAG = "DbAdapter";  
    private DatabaseHelper mDbHelper;  
    private SQLiteDatabase mDb;
    
    private static final String COUNTRY_CREATE =  
            "create table tblcountry (_id integer primary key autoincrement, " 
    				+ "id_c integet not null,"
    		        + "country text not null," + "ecountry text null,"  
            		+ "continent text not null,"+ "capital text not null," 
    		        + "squere text not null," 
                    + "people text not null," + "url text not null,"
                    + "corrency text not null," + "offlang text not null,"
                    + "coordx integer null," + "coordy integer null"
                    + ");";
    private static final String PAPER_CREATE =
            "create table tblpaper (_idp integer primary key autoincrement, "
                    + "id_c integer not null,"
                    + "nominalp text not null," + "yearp text not null,"
                    + "aversp text not null," + "reversp text not null"+ ");";
    
    private static final String COINS_CREATE =
            "create table tblcoins (_idc integer primary key autoincrement, "
                    + "id_c integer not null,"
                    + "yearc text not null," + "infoavers text not null,"
                    + "urlavers text not null," + "inforevers text not null,"
                    + "urlrevers text not null" +");";
                    
    
    public static final String DATABASE_NAME = "AllCountries";  
    private static final String DATABASE_TABLE = "tblcountry";
    private static final String DATABASE_PTABLE = "tblpaper";
    private static final String DATABASE_CTABLE = "tblcoins";
    private static final int DATABASE_VERSION = 4;
    
    private final Context mCtx;
    
    private static class DatabaseHelper extends SQLiteOpenHelper {  
    	   
        DatabaseHelper(Context context) {  
            super(context, DATABASE_NAME, null, DATABASE_VERSION);  
        }  
   
        @Override  
        public void onCreate(SQLiteDatabase db) {  
   
            db.execSQL(COUNTRY_CREATE);
            db.execSQL(PAPER_CREATE);
            db.execSQL(COINS_CREATE);
        }  
   
        @Override  
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {  
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "  
                    + newVersion + ", which will destroy all old data");  
            db.execSQL("DROP TABLE IF EXISTS tblCountry");  
            onCreate(db);  
        }  
    }
    
    public DbAdapter(Context ctx) {  
        this.mCtx = ctx;  
    }  
   
    public DbAdapter open() throws SQLException {  
        mDbHelper = new DatabaseHelper(mCtx);  
        mDb = mDbHelper.getWritableDatabase();  
        return this;  
    }  
   
    public void close() {  
        mDbHelper.close();  

    }
    
    //Begin coins table
    
    public long createCoins(int id_c, String year, String infoavers,
    		String urlavers, String inforevers, String urlrevers) {  
        ContentValues pInitialValues = new ContentValues();
      
        pInitialValues.put(KEY_CSER_ID, id_c);
        pInitialValues.put(KEY_CYEAR, year);
        pInitialValues.put(KEY_INFOAVERS, infoavers);
        pInitialValues.put(KEY_CAVERS, urlavers);
        pInitialValues.put(KEY_INFOREVERS, inforevers);
        pInitialValues.put(KEY_CREVERS, urlrevers);
        
        return mDb.insert(DATABASE_CTABLE, null, pInitialValues);  
    }
    
    public boolean deleteCoins(long rowId) {  
  	  
        return mDb.delete(DATABASE_CTABLE, KEY_CSER_ID + "=" + rowId, null) > 0;  
    }  
    
    public Cursor fetchAllCoins() {
    	return mDb.query(DATABASE_PTABLE, new String[] {KEY_CROWID, KEY_CSER_ID, 
    			KEY_CYEAR, KEY_INFOAVERS, KEY_CAVERS, KEY_INFOREVERS, KEY_CREVERS}, null, null, null, null, null);  
    }
    
    public Cursor fetchCoins(long rowId) throws SQLException {  
      
        Cursor mCursor = mDb.query(true, DATABASE_CTABLE, new String[] {KEY_CROWID, KEY_CSER_ID, 
    			KEY_CYEAR, KEY_INFOAVERS, KEY_CAVERS, KEY_INFOREVERS, KEY_CREVERS},
            			KEY_CSER_ID + "=" + rowId, null,  
                        null, null, null, null);  
        if (mCursor != null) {  
            mCursor.moveToFirst();  
        }  
        return mCursor;  
      
    }
    
    public boolean updateCoins(long rowId, int id_c, String year, String infoavers,
    		String urlavers, String inforevers, String urlrevers) {
    	
        ContentValues args = new ContentValues();
        
        args.put(KEY_CSER_ID, id_c);
        args.put(KEY_CYEAR, year);
        args.put(KEY_INFOAVERS, infoavers);
        args.put(KEY_CAVERS, urlavers);
        args.put(KEY_INFOREVERS, inforevers);
        args.put(KEY_CREVERS, urlrevers);
        
        return mDb.update(DATABASE_CTABLE, args, KEY_CROWID + "=" + rowId, null) > 0;  
    }

	public Cursor fetchCoinsByQuery(String query) {
		
		Cursor mCursor =  mDb.query(true, DATABASE_CTABLE, new String[] {KEY_CROWID, KEY_CSER_ID, 
    			KEY_CYEAR, KEY_INFOAVERS, KEY_CAVERS, KEY_INFOREVERS, KEY_CREVERS}, KEY_CYEAR + " LIKE" + "'%" + query + "%'", null,
				null, null, null, null);
		return mCursor;
    }
	
//End coins table
	
//Begin paper table
    
    public long createPaper(int id_c, String nominal, String year,
    		String avers, String revers) {  
        ContentValues pInitialValues = new ContentValues();
      
        pInitialValues.put(KEY_PSER_ID, id_c);
        pInitialValues.put(KEY_PNOMINAL, nominal);
        pInitialValues.put(KEY_PYEAR, year);
        pInitialValues.put(KEY_PAVERS, avers);
        pInitialValues.put(KEY_PREVERS, revers);
        
        return mDb.insert(DATABASE_PTABLE, null, pInitialValues);  
    }
    
    public boolean deletePaper(long rowId) {  
  	  
        return mDb.delete(DATABASE_PTABLE, KEY_PSER_ID + "=" + rowId, null) > 0;  
    }  
    
    public Cursor fetchAllPaper() {
    	return mDb.query(DATABASE_PTABLE, new String[] {KEY_PROWID, KEY_PSER_ID, KEY_PNOMINAL,
    			KEY_PYEAR, KEY_PAVERS, KEY_PREVERS}, null, null, null, null, null);  
    }
    
    public Cursor fetchPaper(long rowId) throws SQLException {  
      
        Cursor mCursor = mDb.query(true, DATABASE_PTABLE, new String[] {KEY_PROWID, KEY_PSER_ID,
        		KEY_PNOMINAL, KEY_PYEAR, KEY_PAVERS, KEY_PREVERS},
            			KEY_PSER_ID + "=" + rowId, null,  
                        null, null, null, null);  
        if (mCursor != null) {  
            mCursor.moveToFirst();  
        }  
        return mCursor;  
      
    }
    
    public boolean updatePaper(long rowId, int id_c, String nominal, String year,
    		String avers, String revers) {
    	
        ContentValues args = new ContentValues();
        
        args.put(KEY_PSER_ID, id_c);
        args.put(KEY_PNOMINAL, nominal);
        args.put(KEY_PYEAR, year);
        args.put(KEY_PAVERS, avers);
        args.put(KEY_PREVERS, revers);
        
        return mDb.update(DATABASE_PTABLE, args, KEY_PROWID + "=" + rowId, null) > 0;  
    }

	public Cursor fetchPaperByQuery(String query) {
		
		Cursor mCursor =  mDb.query(true, DATABASE_PTABLE, new String[] {KEY_PROWID, KEY_PSER_ID, 
				KEY_PNOMINAL, KEY_PYEAR, KEY_PAVERS, KEY_PREVERS}, KEY_PNOMINAL + " LIKE" + "'%" + query + "%'", null,
				null, null, null, null);
		return mCursor;
    }
	
	//End paper table
	
	//Begin country table
    public long createCountry(int id_c, String country,  String continent,
    		String capital, String squere, String people,
    		String url, String currency, String offlang ,String ecountry) {  
        ContentValues initialValues = new ContentValues();
        
        initialValues.put(KEY_SER_ID, id_c);
        initialValues.put(KEY_COUNTRY_NAME, country);
        initialValues.put(KEY_CONTINENT_NAME, continent);
        initialValues.put(KEY_CAPITAL_NAME, capital);
        initialValues.put(KEY_SQUERE, squere);
        initialValues.put(KEY_PEOPLE, people);
        initialValues.put(KEY_URL_FLAGS, url);
        initialValues.put(KEY_CORRENCY, currency);
        initialValues.put(KEY_OFF_LANG, offlang);
        initialValues.put(KEY_COUNTRY_ENAME, ecountry);

        return mDb.insert(DATABASE_TABLE, null, initialValues);  
    }
    
    public boolean deleteCountry(long rowId) {  
  	  
        return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;  
    }  
    
    public Cursor fetchAllCountry() {
    	return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_SER_ID, KEY_COUNTRY_NAME, KEY_COUNTRY_ENAME,
    			KEY_CONTINENT_NAME, KEY_CAPITAL_NAME, KEY_SQUERE, KEY_PEOPLE, KEY_URL_FLAGS,
    			KEY_CORRENCY, KEY_OFF_LANG, KEY_COORD_X, KEY_COORD_Y}, null, null, null, null, null);  
    }
    
    public Cursor fetchCountry(long rowId) throws SQLException {  
      
        Cursor mCursor =  
      
                mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_SER_ID, KEY_COUNTRY_NAME, KEY_COUNTRY_ENAME,
            			KEY_CONTINENT_NAME, KEY_CAPITAL_NAME, KEY_SQUERE, KEY_PEOPLE, KEY_URL_FLAGS,
            			KEY_CORRENCY, KEY_OFF_LANG, KEY_COORD_X, KEY_COORD_Y},
            			KEY_SER_ID + "=" + rowId, null,  
                        null, null, null, null);  
        if (mCursor != null) {  
            mCursor.moveToFirst();  
        }  
        return mCursor;  
      
    }
    
    public boolean updateCountry(long rowId, String country, String ecountry, String continent,
    		String capital, String squere, String people,
    		String url, String currency, String offlang,
    		int coordx, int coordy) {
    	
        ContentValues args = new ContentValues();
        
        args.put(KEY_COUNTRY_NAME, country);
        args.put(KEY_COUNTRY_ENAME, ecountry);
        args.put(KEY_CONTINENT_NAME, continent);
        args.put(KEY_CAPITAL_NAME, capital);
        args.put(KEY_SQUERE, squere);
        args.put(KEY_PEOPLE, people);
        args.put(KEY_URL_FLAGS, url);
        args.put(KEY_CORRENCY, currency);
        args.put(KEY_OFF_LANG, offlang);
        args.put(KEY_COORD_X, coordx);
        args.put(KEY_COORD_Y, coordy);
      
        return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;  
    }

	public Cursor fetchRecordsByQuery(String query) {
		
		Cursor mCursor =  mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID, KEY_SER_ID, KEY_COUNTRY_NAME, KEY_COUNTRY_ENAME,
    			KEY_CONTINENT_NAME, KEY_CAPITAL_NAME, KEY_SQUERE, KEY_PEOPLE, KEY_URL_FLAGS,
    			KEY_CORRENCY, KEY_OFF_LANG, KEY_COORD_X, KEY_COORD_Y}, KEY_COUNTRY_NAME + " LIKE" + "'%" + query + "%'", null,
				null, null, null, null);
		return mCursor;
    }
	
	public boolean checkData(){
    	Cursor c = fetchAllCountry();
    	Cursor p = fetchAllPaper();
    	if (c.moveToFirst() || p.moveToFirst())
    		return true;
    	else
    		return false;
    }
	// End country table
	
	
}